﻿-- =============================================
-- Author:		顾世杰
-- Create date: 2013.11.11
-- Description:	搜索客户,维护客服
-- =============================================
CREATE PROCEDURE [dbo].[proc_WHCustomer_Search]
	(
		@companyid int,		
		@CusName nvarchar(20),--客户名称或员工名
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	Declare @cSql nvarchar(4000)
	Set @cSql = '
    ;WITH list1 As (
		SELECT ROW_NUMBER() OVER (ORDER BY ServicePersonEname,Id DESC) AS Row,
		CustomerId,CustomerName,Project,
		SourceId,IndustryOneId,TypeId,ProtectId,
		IsNull(ServicePersonEName,'''') as ServicePersonEName
		From customer
		Where CompanyId='+Convert(varchar(10),@companyid)+' And audit=1 And delflag=0
		And (ServicePersonEname='''+@CusName+''' Or CustomerName like ''%'+@CusName+'%'')
	)

	Select *,(Select count(1) from list1 ) as RecordCount,
	IsNull((Select Title From Customer_Source Where Id=list1.SourceId),''...'') As Source,
	IsNull((Select Title From Customer_Industry Where Id=list1.IndustryOneId),''...'') As IndustryOne,
	IsNull((Select Title From Customer_Type Where Id=list1.TypeId),''...'') As Type,
	IsNull((Select Title From Customer_Protect Where Id=list1.ProtectId),''...'') As Protect
	From list1
	Where row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex)+' Order by Row'

	Exec(@cSql)
--    ;WITH list1 as (
--	SELECT  
--	 ROW_NUMBER() OVER (ORDER BY ServicePersonEname,customer.id DESC) AS Row,
--		 CustomerId,CustomerName,Project,	
--		SourceId,
--		IndustryOneId,
--		TypeId,	
--		ProtectId,
--		isnull(ServicePersonEName,'') as ServicePersonEName
--		
--	 from customer where customer.companyid=@companyid and audit=1 and delflag=0 and 
--	( ServicePersonEname = ''+@CusName+'' or CustomerName like '%'+@CusName+'%')		
--	)
--		
--	select *,(select count(1) from list1 ) as RecordCount,
--	
--		isnull((Select Title From Customer_Source Where Id=list1.SourceId),'...') As Source,
--		isnull((Select Title From Customer_Industry Where Id=list1.IndustryOneId),'...') As IndustryOne,
--		isnull((Select Title From Customer_Type Where Id=list1.TypeId),'...') As Type,
--	
--		ISNULL((Select Title From Customer_Protect Where Id=list1.ProtectId),'...') As Protect
--
--	 from list1 where row between @StartIndex and @EndIndex order by Row
End
